﻿namespace HuaWei.CommClass
{
    using System;

    public static class CRepSQL
    {
        public static string DEL_GCBG = "\r\ndelete from REP_GCBG where bdid='{0}' and qcid={1}";
        public static string DEL_ZQZFZS = "\r\ndelete from REP_ZQZFZS where bdid='{0}' and qcid={1}";
        public static string GCBG = "\r\nselect * from REP_GCBG where bdid='{0}' and qcid={1}";
        public static string GCBG_1 = "select qdbh,pzbh from (\r\nselect a.id,qdbh,b.pzbh from bgqdjl_mx a left join bgqdjl_m b on a.pid=b.id where a.bdid='{0}' and a.qcid<=1 \r\n) a where pzbh is not null group by qdbh,pzbh order by pzbh";
        public static string GCBG_2 = "select qdbh,bglh from (\r\nselect a.id,qdbh,bglh from BGLQD a left join BGL b on a.pid=b.id where a.bdid='{0}' \r\n) a where bglh is not null group by qdbh,bglh order by bglh";
        public static string INSERT_GCBG_QD = "\r\ninsert into REP_GCBG (id,qdbh,qdmc,qdlx,htsl,htdj,bgsl,bqmjl,sqmjl,bqjl,lx,dw,bdid,qcid)\r\nselect newid() as id,a.qdbh,qdmc,qdlx,htsl,zbdj,[c.bgs],[b.bqmjls],[d.sqjls],[e.bqjls],lx,dw,'{0}' as bdid,{1} as qcid from \r\n(select qdbh,qdmc,qdlx,htsl,zbdj,0 as lx,dw from jlqd where bdid='{0}') a\r\nleft join \r\n(select qdbh,sum([BGZJL]) as bgs from BGLQD where bdid='{0}' group by qdbh) c\r\non a.qdbh=c.qdbh\r\n left join \r\n(select qdbh,sum([bqjls]) as bqmjls from bgqdjl_mx where bdid='{0}' and qcid<={1} group by qdbh) b\r\non a.qdbh=b.qdbh\r\n left join \r\n(select qdbh,sum([bqjls]) as sqjls from bgqdjl_mx where bdid='{0}' and qcid<={1}-1 group by qdbh) d\r\non a.qdbh=d.qdbh\r\n left join \r\n(select qdbh,sum([bqjls]) as bqjls from bgqdjl_mx where bdid='{0}' and qcid={1} group by qdbh) e\r\non a.qdbh=e.qdbh  ";
        public static string INSERT_ZQZFZS_BZ = "insert into REP_ZQZFZS (id,qdbh,qdmc,qdlx,htsl,htdj,htje,bgje,bghje,bqjlje,sqmjlje,bqmjlje,lx,dw,bdid,qcid,yjd)\r\nselect newid() as id,aa.bz,bb.qdmc,bb.qdlx,bb.htsl,bb.zbdj,aa.htje,aa.bgje,aa.bghje,aa.bqjlje,aa.sqmjlje,aa.bqmjlje,0 as lx,bb.dw,'{0}',{1} as qcid,0 as yjd from (\r\nselect bz + '00' as bz,sum(HTJE) as HTJE,sum(BGJE) as BGJE,sum(BGHJE) as BGHJE,sum(BQJLJE) as BQJLJE,sum(SQMJLJE) as SQMJLJE,sum(BQMJLJE) as BQMJLJE from (\r\nselect substring(qdbh,1,1) as BZ,HTJE,BGJE,BGHJE,BQJLJE,SQMJLJE,BQMJLJE from REP_ZQZFZS where lx=0 and bdid='{0}' and qcid={1}) a group by bz\r\n) aa left join jlqd bb on aa.bz=bb.qdbh where bb.bdid={0}";
        public static string INSERT_ZQZFZS_CFKLX = "\r\ninsert into REP_ZQZFZS (id,qdmc,bqmjlje,sqmjlje,bqjlje,LX,bdid,qcid,yjd)\r\nselect newid() as id,'迟付款利息' as qdmc,sum(bqmjlje) as bqmjlje,sum(sqjlje) as sqjlje,sum(bqjlje) as bqjlje,5 as LX\r\n,'{0}' as bdid,{1} as qcid,2 as yjd\r\n from (\r\nselect sum([yflx]) as bqmjlje,0 as sqjlje,0 as bqjlje from qtjl_cfklx where bdid='{0}' and qcid<={1}\r\nunion all\r\nselect 0 as bqmjlje,sum([yflx]) as sqjlje,0 as bqjlje from qtjl_cfklx where bdid='{0}'  and qcid<={1}-1\r\nunion all\r\nselect 0 as bqmjlje,0 as sqjlje,sum([yflx]) as bqjlje from qtjl_cfklx where bdid='{0}'  and qcid={1}\r\n) a";
        public static string INSERT_ZQZFZS_CLYFK = "\r\ninsert into REP_ZQZFZS (id,qdmc,bqmjlje,sqmjlje,bqjlje,LX,bdid,qcid,yjd)\r\nselect newid() as id,'材料预付款' as qdmc,ljdfje,sqmdfje,bqdfje,13 as LX\r\n,'{0}' as bdid,{1} as qcid,3 as yjd\r\n from QTJL_CLYFK where bdid='{0}' and qcid={1}";
        public static string INSERT_ZQZFZS_CLYFK_KC = "\r\ninsert into REP_ZQZFZS (id,qdmc,bqmjlje,sqmjlje,bqjlje,LX,bdid,qcid,yjd)\r\nselect newid() as id,'扣回材料预付款' as qdmc,sum([bqmjlje])*-1 as bqmjlje,sum([sqjlje])*-1 as sqjlje,sum([bqjlje])*-1 as bqjlje,14 as LX\r\n,'{0}' as bdid,{1} as qcid,3 as yjd\r\n from (\r\nselect sum([BQMKHJE]) as bqmjlje,sum([SQMKHJE]) as sqjlje,sum([BQKHJE]) as bqjlje from QTJL_CLYFK where bdid='{0}' and qcid<={1}\r\n) a";
        public static string INSERT_ZQZFZS_DYYFK = "\r\ninsert into REP_ZQZFZS (id,qdmc,htje,bqmjlje,sqmjlje,bqjlje,LX,bdid,qcid,yjd)\r\nselect newid() as id,'动员预付款' as qdmc,dyyfkze,bqmzfdyyfk,yfdyyfk,bqsqdyyfk,11 as LX \r\n,'{0}' as bdid,{1} as qcid,3 as yjd from qtjl_dyyfk where bdid='{0}'  and qcid={1}\r\n";
        public static string INSERT_ZQZFZS_DYYFK_KC = "\r\ninsert into REP_ZQZFZS (id,qdmc,bqmjlje,sqmjlje,bqjlje,LX,bdid,qcid,yjd)\r\nselect newid() as id,'扣回动员预付款' as qdmc,sum(bqmjlje)*-1 as bqmjlje,sum(sqjlje)*-1 as sqjlje,sum(bqjlje)*-1 as bqjlje,12 as LX\r\n,'{0}' as bdid,{1} as qcid,3 as yjd\r\n from (\r\nselect sum([BQKCDYYFK]) as bqmjlje,0 as sqjlje,0 as bqjlje from qtjl_dyyfk where bdid='{0}' and qcid<={1}\r\nunion all\r\nselect 0 as bqmjlje,sum([BQKCDYYFK]) as sqjlje,0 as bqjlje from qtjl_dyyfk where bdid='{0}'  and qcid<={1}-1\r\nunion all\r\nselect 0 as bqmjlje,0 as sqjlje,sum([BQKCDYYFK]) as bqjlje from qtjl_dyyfk where bdid='{0}'  and qcid={1}\r\n) a";
        public static string INSERT_ZQZFZS_HJ = "\r\ninsert into REP_ZQZFZS (id,qdmc,HTJE,BGJE,BGHJE,BQJLJE,SQMJLJE,BQMJLJE,LX,bdid,qcid,yjd)\r\nselect newid() as id,'合计' as qdmc,sum(HTJE) as HTJE,sum(BGJE) as BGJE,sum(BGHJE) as BGHJE,sum(BQJLJE) as BQJLJE,sum(SQMJLJE) as SQMJLJE,sum(BQMJLJE) as BQMJLJE\r\n,10 as LX,'{0}' as bdid,{1} as qcid,4 as yjd\r\n from REP_ZQZFZS where (yjd=-1 or yjd=2) and bdid='{0}' and qcid={1}";
        public static string INSERT_ZQZFZS_JGTZ = "\r\ninsert into REP_ZQZFZS (id,qdmc,bqmjlje,sqmjlje,bqjlje,LX,bdid,qcid,yjd)\r\nselect newid() as id,'价格调整' as qdmc,sum(bqmjlje) as bqmjlje,sum(sqjlje) as sqjlje,sum(bqjlje) as bqjlje,2 as LX\r\n,'{0}' as bdid,{1} as qcid,2 as yjd\r\n from (\r\nselect sum([pcje]) as bqmjlje,0 as sqjlje,0 as bqjlje from qtjl_jgtz where bdid='{0}' and qcid<={1}\r\nunion all\r\nselect 0 as bqmjlje,sum([pcje]) as sqjlje,0 as bqjlje from qtjl_jgtz where bdid='{0}'  and qcid<={1}-1\r\nunion all\r\nselect 0 as bqmjlje,0 as sqjlje,sum([pcje]) as bqjlje from qtjl_jgtz where bdid='{0}'  and qcid={1}\r\n) a ";
        public static string INSERT_ZQZFZS_QD = "\r\ninsert into REP_ZQZFZS (id,qdbh,qdmc,qdlx,htsl,htdj,bgsl,bqmjl,sqmjl,bqjl,lx,dw,bdid,qcid,yjd)\r\nselect newid() as id,a.qdbh,qdmc,qdlx,htsl,zbdj,[c.bgs],[a.bqmjls],[d.sqjls],[e.bqjls],lx,dw,'{0}' as bdid,{1} as qcid,1 as yjd from \r\n(select qdbh,sum([bqjls]) as bqmjls from qdjl_mx where bdid='{0}' and qcid<={1} group by qdbh) a\r\nleft join \r\n(select qdbh,sum([BGZJL]) as bgs from BGLQD where bdid='{0}' group by qdbh) c\r\non a.qdbh=c.qdbh\r\n left join \r\n(select qdbh,qdmc,qdlx,htsl,zbdj,0 as lx,dw from jlqd where bdid='{0}') b\r\non a.qdbh=b.qdbh\r\n left join \r\n(select qdbh,sum([bqjls]) as sqjls from qdjl_mx where bdid='{0}' and qcid<={1}-1 group by qdbh) d\r\non a.qdbh=d.qdbh\r\n left join \r\n(select qdbh,sum([bqjls]) as bqjls from qdjl_mx where bdid='{0}' and qcid={1} group by qdbh) e\r\non a.qdbh=e.qdbh  ";
        public static string INSERT_ZQZFZS_QD1 = "\r\ninsert into REP_ZQZFZS (id,qdbh,qdmc,qdlx,lx,dw,bdid,qcid,yjd)\r\nselect newid() as id,qdbh,qdmc,qdlx,0 as lx,dw,bdid,{1} as qcid,-2 as yjd \r\n from jlqd where cjm<>0 and id in (\r\n(\r\nselect id from jlqd where id in (\r\nselect pid from jlqd where bdid='{0}' and qdbh in (\r\nselect qdbh from REP_ZQZFZS where bdid='{0}' and qcid={1}))\r\nunion all\r\nselect id from jlqd where id in (\r\nselect pid from jlqd where id in (\r\nselect pid from jlqd where bdid='{0}' and qdbh in (\r\nselect qdbh from REP_ZQZFZS where bdid='{0}' and qcid={1})))\r\nunion all\r\nselect id from jlqd where id in (\r\nselect pid from jlqd where id in (\r\nselect pid from jlqd where id in (\r\nselect pid from jlqd where bdid='{0}' and qdbh in (\r\nselect qdbh from REP_ZQZFZS where bdid='{0}' and qcid={1}))))\r\nunion all\r\nselect id from jlqd where id in (\r\nselect pid from jlqd where id in (\r\nselect pid from jlqd where id in (\r\nselect pid from jlqd where id in (\r\nselect pid from jlqd where bdid='{0}' and qdbh in (\r\nselect qdbh from REP_ZQZFZS where bdid='{0}' and qcid={1})))))\r\n)\r\n)";
        public static string INSERT_ZQZFZS_QD2 = "\r\ninsert into REP_ZQZFZS (id,qdbh,qdmc,qdlx,htsl,htje,bgsl,bghje,bqmjl,sqmjl,bqjl,lx,dw,bdid,qcid,yjd)\r\nselect newid() as id,qdbh,qdmc,qdlx,htsl,htje,0 as bgs,htje as bghje,0 as bqmjls,0 as sqjls,0 as bqjls,0 as lx,dw,bdid,{1} as qcid,-1 as yjd \r\n from jlqd where cjm=0 and bdid='{0}'\r\n";
        public static string INSERT_ZQZFZS_SPJE = "\r\ninsert into REP_ZQZFZS (id,qdmc,bqmjlje,sqmjlje,bqjlje,LX,bdid,qcid,yjd)\r\nselect newid() as id,'索陪金额' as qdmc,sum(bqmjlje) as bqmjlje,sum(sqjlje) as sqjlje,sum(bqjlje) as bqjlje,3 as LX\r\n,'{0}' as bdid,{1} as qcid,2 as yjd\r\n from (\r\nselect sum([spje]) as bqmjlje,0 as sqjlje,0 as bqjlje from qtjl_spje where bdid='{0}' and qcid<={1}\r\nunion all\r\nselect 0 as bqmjlje,sum([spje]) as sqjlje,0 as bqjlje from qtjl_spje where bdid='{0}'  and qcid<={1}-1\r\nunion all\r\nselect 0 as bqmjlje,0 as sqjlje,sum([spje]) as bqjlje from qtjl_spje where bdid='{0}'  and qcid={1}\r\n) a";
        public static string INSERT_ZQZFZS_WYFJ = "\r\ninsert into REP_ZQZFZS (id,qdmc,bqmjlje,sqmjlje,bqjlje,LX,bdid,qcid,yjd)\r\nselect newid() as id,'违约罚金' as qdmc,sum(bqmjlje)*-1 as bqmjlje,sum(sqjlje)*-1 as sqjlje,sum(bqjlje)*-1 as bqjlje,4 as LX\r\n,'{0}' as bdid,{1} as qcid,2 as yjd\r\n from (\r\nselect sum([wyfj]) as bqmjlje,0 as sqjlje,0 as bqjlje from qtjl_wyfj where bdid='{0}' and qcid<={1}\r\nunion all\r\nselect 0 as bqmjlje,sum([wyfj]) as sqjlje,0 as bqjlje from qtjl_wyfj where bdid='{0}'  and qcid<={1}-1\r\nunion all\r\nselect 0 as bqmjlje,0 as sqjlje,sum([wyfj]) as bqjlje from qtjl_wyfj where bdid='{0}'  and qcid={1}\r\n) a";
        public static string INSERT_ZQZFZS_XJ = "\r\ninsert into REP_ZQZFZS (id,qdmc,HTJE,BGJE,BGHJE,BQJLJE,SQMJLJE,BQMJLJE,LX,bdid,qcid,yjd)\r\nselect newid() as id,'小计' as qdmc,sum(HTJE) as HTJE,sum(BGJE) as BGJE,sum(BGHJE) as BGHJE,sum(BQJLJE) as BQJLJE,sum(SQMJLJE) as SQMJLJE,sum(BQMJLJE) as BQMJLJE\r\n,1 as LX,'{0}' as bdid,{1} as qcid,4 as yjd\r\n from REP_ZQZFZS where yjd=-1 and bdid='{0}' and qcid={1}";
        public static string INSERT_ZQZFZS_ZF = "\r\ninsert into REP_ZQZFZS (id,qdmc,BQJLJE,SQMJLJE,BQMJLJE,LX,bdid,qcid,yjd)\r\nselect newid() as id,'支付' as qdmc,sum([BQJLJE]) as BQJLJE,sum([SQMJLJE]) as SQMJLJE,sum([BQMJLJE]) as BQMJLJE\r\n,20 as LX,'{0}' as bdid,{1} as qcid,4 as yjd\r\n from REP_ZQZFZS where (yjd=-1 or yjd=2 or yjd=3) and bdid='{0}' and qcid={1}";
        public static string REP02 = "\r\nselect * from REP_ZQZFZS where  (yjd=-1 or yjd=2 or yjd=3 or yjd=4) \r\n and bdid='{0}' and qcid={1} order by lx,qdbh";
        public static string REP02_back = "\r\nselect * from REP_ZQZFZS where  ((qdlx<>1 and len(qdbh)=3) or (lx<>0))\r\n and bdid='{0}' and qcid={1} order by ,qdbh";
        public static string REP03 = "\r\nselect * from REP_ZQZFZS where bdid='{0}' and qcid={1} and lx=0 and (yjd=1 or yjd=-2)  order by qdbh";
        public static string REP04_1 = "\r\nselect * from REP_ZQZFZS where bdid='{0}' and qcid={1} and lx=0 and qdlx=1 order by qdbh";
        public static string REP04_2 = "select qdbh,pzbh from (\r\nselect a.id,qdbh,pzbh from qdjl_mx a left join qdjl_m b on a.pid=b.id where a.bdid='{0} and a.qcid<=1' \r\n) a where pzbh is not null group by qdbh,pzbh order by pzbh";
        public static string REP05 = "\r\nselect * from REP_GCBG where bdid='{0}' and qcid={1}";
        public static string REP07 = "\r\nselect * from QTJL_JGTZMX where bdid='{0}' and qcid={1}";
        public static string REP09 = "\r\nselect * from QTJL_CLYFK where bdid='{0}' and qcid<={1}";
        public static string REP10 = "\r\nselect * from QTJL_DYYFK where bdid='{0}'  and qcid={1}";
        public static string REP11_1 = "select a.*,b.maxct as CT,'' as jss from qdjl_m a left join gclct b on a.jlct=ctlj where a.bdid='{0}' and qcid={1}";
        public static string REP11_2 = "select * from qdjl_jss where bdid='{0}' and qcid={1}";
        public static string REP11_30 = "select  * from QDJL_MX  where bdid='{0}' and qcid={1} order by hvs desc";
        public static string REP11_31 = "select ID,PID,QDBH,QDMC,DW,BQJLS as BQJLS1,0 BQJLS from QDJL_MX  where bdid='{0}' and qcid={1} order by hvs desc";
        public static string REP12_1 = "select qdbh,pzbh from (\r\nselect a.id,qdbh,pzbh from qdjl_mx a left join qdjl_m b on a.pid=b.id where a.bdid='{0}' and a.qcid={1} \r\n) a where pzbh is not null group by qdbh,pzbh order by pzbh";
        public static string REP12_2 = "select a.qdbh,a.qdmc,dw,zbdj as dj,'' as pzh,b.bqjl as sl,b.bqjlje as je,yjd from jlqd a right join\r\n(select qdbh,sum(bqjl) as bqjl,sum(bqjlje) as bqjlje,yjd  \r\n from REP_ZQZFZS where (yjd=1 or yjd=-2) and qdbh<>'' and bdid='{0}'\r\n and qcid={1} group by qdbh,yjd) b on a.qdbh=b.qdbh and  a.bdid='{0}'";
        public static string UPDATE_ZQZFZS = " \r\nupdate REP_ZQZFZS set bgje=round(bgsl*htdj,0),bghsl=htsl+bgsl,bghje=round((htsl+bgsl)*htdj,0),bqjlje=round(bqjl*htdj,0),sqmjlje=round(sqmjl*htdj,0),bqmjlje=round(bqmjl*htdj,0),zjlx=substring(qdbh,1,1)  \r\n where bdid='{0}' and qcid={1} and yjd=1";
        public static string UPDATE_ZQZFZS_ZJ = " \r\nupdate REP_ZQZFZS set yjd=-1 where qdbh=zjlx+'00' or qdbh=zjlx\r\nand bdid='{0}' and qcid={1}";
        public static string ZQZFZS = "\r\nselect * from REP_ZQZFZS where bdid='{0}' and qcid={1}";
        public static string ZQZFZS_1 = "select qdbh,pzbh from (\r\nselect a.id,qdbh,b.pzbh from qdjl_mx a left join qdjl_m b on a.pid=b.id where a.bdid='{0}' and a.qcid={1} \r\n) a where pzbh is not null group by qdbh,pzbh order by pzbh";
        public static string ZQZFZS_2 = "select qdbh,qqzh from (\r\nselect a.id,qdbh,b.qqzh from qdjl_mx a left join qdjl_m b on a.pid=b.id where a.bdid='{0}' and a.qcid={1}\r\n) a where qqzh is not null group by qdbh,qqzh order by qqzh";
        public static string ZQZFZS_BZ = "\r\nselect bz + '00' as bz,sum(HTJE) as HTJE,sum(BGJE) as BGJE,sum(HTJE) as BGHJE,sum(BQJLJE) as BQJLJE,sum(SQMJLJE) as SQMJLJE,sum(BQMJLJE) as BQMJLJE from (\r\nselect substring(qdbh,1,1) as BZ,0 as HTJE,BGJE,BGHJE,BQJLJE,SQMJLJE,BQMJLJE from REP_ZQZFZS where yjd=1 and bdid='{0}' and qcid={1}) a group by bz\r\n";
        public static string ZQZFZS_BZ_back = "select bz + '00' as bz,sum(HTJE) as HTJE,sum(BGJE) as BGJE,sum(BGHJE) as BGHJE,sum(BQJLJE) as BQJLJE,sum(SQMJLJE) as SQMJLJE,sum(BQMJLJE) as BQMJLJE from (\r\nselect substring(qdbh,1,1) as BZ,HTJE,BGJE,BGHJE,BQJLJE,SQMJLJE,BQMJLJE from REP_ZQZFZS where lx=0 and bdid='{0}' and qcid={1}) a group by bz\r\n";
    }
}

